package com.windy.spring.dao.jpa;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.PersistenceContextType;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.windy.spring.dao.UtilityDaoStatistics;
import com.windy.spring.data.statistics.StatisticsSpot;


@Repository
public class UtilityJpaDaoStatistics implements UtilityDaoStatistics{
	
	//private static final Logger logger = LoggerFactory.getLogger(UtilityJpaDaoStatistics.class);
	 
	@PersistenceContext(type=PersistenceContextType.EXTENDED)
	protected EntityManager entityManager;
	
	public List<StatisticsSpot> getSpotStatistics(Integer userId, Integer pageNumber, Integer rowsPerPage){
		String queryString=""+
				"SELECT fs.spot_idspot AS spot, favoriteCount.totalfavs as totalFavs, favoriteCount.mediaFavoritos AS avgFavs, favoriteCount.mediaTotalUsers AS avgUsers, favoriteCount.totalusers AS totalUsers "+
				"FROM favorite_spot fs "+
				    "LEFT JOIN (" +
				    	"SELECT s1.id_spot, fuc.totalfavs, funa.mediaFavoritos, una.mediaTotalUsers, uc.totalusers "+
				    	"FROM spot s1 "+
				    		"LEFT JOIN v_favspots_favuserscount fuc ON (fuc.id_spot=s1.id_spot) "+
				    		"LEFT JOIN v_favspots_favusersnodeavg funa ON (funa.id_spot=s1.id_spot) "+
				    		"LEFT JOIN v_favspots_usernodeavg una ON (una.id_spot=s1.id_spot) "+
				    		"LEFT JOIN v_favspots_userscount uc ON (uc.id_spot=s1.id_spot) "+
				    	"WHERE "+
				    		"fuc.user_iduser=:userId and "+
				    		"fuc.user_iduserfrom=:userId and "+
				    		"funa.user_iduserfrom=:userId and "+
				    		"uc.user_iduser=:userId" +
				    	") AS favoriteCount ON (fs.spot_idspot=favoriteCount.id_spot) "+
				"WHERE "+
					"fs.user_iduser=:userId " +
				"ORDER BY favoriteCount.totalfavs, favoriteCount.mediaFavoritos, favoriteCount.mediaTotalUsers, favoriteCount.totalusers " +
				"LIMIT :startLimit,:rowsPerPage";
	
		Query query = entityManager.createNativeQuery(queryString, StatisticsSpot.class);
		query.setParameter("userId", userId);
		query.setParameter("startLimit", pageNumber*rowsPerPage);
		query.setParameter("rowsPerPage", rowsPerPage);
		
		return query.getResultList();
	}
 
}
